package com.dbapp.garden.common;

import com.dbapp.garden.common.com.dbapp.garden.common.exception.BusinessException;
import com.dbapp.garden.entity.GardenStudentInfo;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.ResourceUtils;

import java.io.*;
import java.lang.reflect.InvocationTargetException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author by gangzi on 2017/11/10.
 */
public class ExcelUtils {

    private static final Logger log = LoggerFactory.getLogger(ExcelUtils.class);

    private static Map<String, FieldName> fieldNameMap = new HashMap<>();
    private static Map<String, FieldName> fieldNameMap2 = new HashMap<>();
    private static Map<String, FieldName> uploadFieldNameMap = new HashMap<>();

    static {
        File file = null;
        try {
            file = ResourceUtils.getFile("classpath:poi/template_field.json");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        ObjectMapper mapper = new ObjectMapper();
        try {
            List<FieldName> fieldNameList = mapper.readValue(file, new TypeReference<List<FieldName>>() {
            });
            fieldNameList.forEach(f -> {
                fieldNameMap.put(f.getKey(), f);
                fieldNameMap2.put(f.getField(), f);
            });
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    public static XSSFWorkbook generateTemplate(String[] ids, Map<String, String[]> dropdownData) {

        try {
            XSSFWorkbook wb = new XSSFWorkbook();
            XSSFSheet sheet = wb.createSheet("sheet1");
            sheet.setDefaultColumnWidth(20);
            Row row = sheet.createRow((short) 0);
            // Create a cell and put a value in it.
            XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);

            if (ids != null && ids.length > 0) {
                for (int i = 0; i < ids.length; i++) {
                    String key = ids[i];
                    if (StringUtils.isEmpty(key)) {
                        continue;
                    }

                    FieldName filedName = fieldNameMap.get(key);
                    if (filedName == null) {
                        continue;
                    }
                    if (filedName.getType() == 1) {

                        if (dropdownData == null || dropdownData.get(key) == null) {
                            Cell cell = row.createCell(i);
                            cell.setCellType(CellType.STRING);
                            cell.setCellValue(filedName.getField());
                        } else {

                            //需要设置下拉框
                            Cell cell = row.createCell(i);
                            cell.setCellType(CellType.STRING);
                            cell.setCellValue(filedName.getField());
                            String[] data = dropdownData.get(key);
                            XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(data);
                            CellRangeAddressList addressList = new CellRangeAddressList(0, 65535, i, i);
                            XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
                            validation.setSuppressDropDownArrow(true);
                            validation.setShowErrorBox(true);
                            sheet.addValidationData(validation);
                        }

                    } else {
                        Cell cell = row.createCell(i);
                        cell.setCellType(CellType.STRING);
                        cell.setCellValue(filedName.getField());
                    }

                }
            }
            return wb;
//
        } catch (Exception e) {
            log.info("创建模版失败。。。");
            e.printStackTrace();
            throw new RuntimeException("创建模版失败。。。");
        }
    }

    public static void main(String[] args) {
        String[] ids = {"1", "2", "3", "4", "5", "6", "7", "8"};
        String[] parent = {"爸爸", "妈妈", "爷爷", "奶奶"};
        Map<String, String[]> dropdownData = new HashMap<>(16);
        dropdownData.put("4", parent);
        XSSFWorkbook workbook = generateTemplate(ids, dropdownData);

    }


    public static String jsonFile2String(File file) {
        StringBuilder result = new StringBuilder();
        try {
            BufferedReader br = new BufferedReader(new FileReader(file));//构造一个BufferedReader类来读取文件

            //使用readLine方法，一次读一行
            String s;
            while ((s = br.readLine()) != null) {
                result.append(System.lineSeparator() + s);
            }
            br.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result.toString();
    }

    public static List<GardenStudentInfo> getDataFromExcel(Workbook wb) {

        List<GardenStudentInfo> studentInfoList = new ArrayList<>();

        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            Sheet sheet = wb.getSheetAt(i);
            System.out.println(sheet.getLastRowNum());
            System.out.println(sheet.getSheetName());
            for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {
                if (j == 0) {
                    XSSFRow row = (XSSFRow) sheet.getRow(j);
                    boolean isAvail = checkTitle(row);
                    if (isAvail) {
                        log.info("模版文件标题校验通过。。。");
                        continue;
                    } else {
                        throw new BusinessException("导入的Excel文件不是标准的模版文件，请下载标准的模版文件");
                    }
                }
                XSSFRow row = (XSSFRow) sheet.getRow(j);
                GardenStudentInfo studentInfo = new GardenStudentInfo();
                //遍历Excel的每一列
                for (int k = 0; k < row.getPhysicalNumberOfCells(); k++) {
                    XSSFCell cell = row.getCell(k);
                    FieldName f = uploadFieldNameMap.get(String.valueOf(k));
                    String excelValue = getCellValue(cell);
                    try {
                        BeanUtils.setProperty(studentInfo, f.getPropName(), excelValue);
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                        log.info("属性：" + f.getPropName() + "设置数据异常！1");
                        continue;
                    } catch (InvocationTargetException e) {
                        e.printStackTrace();
                        log.info("属性：" + f.getPropName() + "设置数据异常！2");
                        continue;
                    }
                    log.debug(f.getField() + ":" + f.getPropName() + ":" + getCellValue(cell));
                }
                studentInfoList.add(studentInfo);
            }
        }

        return studentInfoList;
    }

    private static boolean checkTitle(XSSFRow row) {
        if (row == null) {
            return false;
        }
        for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
            XSSFCell cell = row.getCell(i, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
            String t = getCellValue(cell);
            if (!fieldNameMap2.containsKey(t)) {
                return false;
            }
            uploadFieldNameMap.put(String.valueOf(i), fieldNameMap2.get(t));
        }

        return true;
    }


    public static String getCellValue(XSSFCell cell) {
        if (cell == null) {
            return "";
        }
        CellType cellType = cell.getCellTypeEnum();
        switch (cellType) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                DecimalFormat format = new DecimalFormat("#");
                return String.valueOf(format.format(cell.getNumericCellValue()));
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";


        }
    }

}
